package org.neuedu.dao;

import org.neuedu.bean.Article;
import org.neuedu.bean.Role;
import org.neuedu.bean.User;
import org.neuedu.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    @Override
    public int serchUserByRegEmail(String email) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select count(*) counts from user where email = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, email);
            rs = ps.executeQuery();
            if(rs.next()){
                count = rs.getInt("counts");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    @Override
    public int saveUserInfo(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "insert into user(email,password,nickname) values (?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getEmail());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getNickName());
            count = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    @Override
    public int saveRoleUserInfo(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "insert into roleuser(uid,rid) values (?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, user.getId());
            ps.setInt(2, 2);
            count = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    @Override
    public User serchUidByEmail(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User u = null;
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select id from user where email = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getEmail());
            rs = ps.executeQuery();
            if(rs.next()){
                u = new User();
                u.setId(rs.getInt("id"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return u;
    }

    @Override
    public User serchUserByEmailAndPass(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User u = null;
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select * from user where email = ? and password = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,user.getEmail());
            ps.setString(2,user.getPassword());
            rs = ps.executeQuery();
            if(rs.next()){
                u = new User();
                u.setId(rs.getInt("id"));
                u.setEmail(user.getEmail());
                u.setPassword(user.getPassword());
                u.setRegTime(rs.getDate("regtime"));
                u.setNickName(rs.getString("nickname"));
                u.setAvatar(rs.getString("avatar"));
                u.setCity(rs.getString("city"));
                u.setEnable(rs.getBoolean("enable"));
                u.setGender(rs.getBoolean("gender"));
                u.setKissNum(rs.getInt("kissnum"));
                u.setTime(rs.getDate("time"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return u;
    }

    @Override
    public List<Role> serchRolesByUid(Integer uid) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Role> roles = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select r.id id,r.name name,r.namezh namezh\n" +
                    "from role r\n" +
                    "join roleuser ru\n" +
                    "on r.id = ru.rid\n" +
                    "where uid = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,uid);
            rs = ps.executeQuery();
            while (rs.next()) {
                Role role = new Role();
                role.setId(rs.getInt("id"));
                role.setName(rs.getString("name"));
                role.setNameZh(rs.getString("namezh"));
                roles.add(role);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return roles;
    }

    @Override
    public int getUserById(Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql =
                    "select DATE_FORMAT(time,'%Y-%m-%d') time from user \n" +
                            "where DATE_FORMAT(time,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')\n" +
                            "and id = ? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            if (rs.next()) {
                count = 1;
            }else {
                count = 0;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    @Override
    public int updatetimeById(Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
       int count=0;
        try {
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql =
                    "update user set  time = DATE_FORMAT(now(),'%Y-%m-%d') where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            count = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    @Override
    public int updateKissById(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "update user set kissnum = kissnum +5 where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, user.getId());
            count = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    @Override
    public int updateavatar(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try{
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "update user set avatar = ? where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getAvatar());
            ps.setInt(2, user.getId());
            count = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    @Override
    public int updateUserInfo(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "update user set nickname = ? ,gender = ? , city = ? , sign = ?" +
                    " where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,user.getNickName());
            ps.setBoolean(2,user.getGender());
            ps.setString(3,user.getCity());
            ps.setString(4,user.getSign());
            ps.setInt(5, user.getId());
            count = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    @Override
    public int updatePasswordInfo(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count=0;
        try {
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "UPDATE user \n" +
                    " set password=? \n" +
                    "where id=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,user.getPassword());
            ps.setInt(2,user.getId());
            count = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }
}
